﻿Imports DTO
Imports System.Data.SqlClient
Public Class NhanVienDAO
    Public Shared Function LayDanhSachNhanVien() As DataTable
        Dim dt As New DataTable
        dt = CKetNoi.GetDataTableByStoreProc("LayDSNhanVien")
        Return dt
    End Function

    Public Shared Function LayDSNhanVienCBX() As DataTable
        Return CKetNoi.GetData("select MaNhanVien, TenNhanVien from NhanVien")
    End Function

    Public Shared Function ThemNhanVien(NhanVienDTO As NhanVienDTO) As Integer
        Dim kt As Integer
        Dim lstVal As New List(Of System.Data.SqlClient.SqlParameter)

        lstVal.Add(New System.Data.SqlClient.SqlParameter("@tenNV", NhanVienDTO.TenNhanVien))
        lstVal.Add(New System.Data.SqlClient.SqlParameter("@maBP", NhanVienDTO.MaBoPhan))
        If NhanVienDTO.MaNguoiQL = Nothing Then
            lstVal.Add(New System.Data.SqlClient.SqlParameter("@maNQL", DBNull.Value))
        Else
            lstVal.Add(New System.Data.SqlClient.SqlParameter("@maNQL", NhanVienDTO.MaNguoiQL))
        End If
        lstVal.Add(New System.Data.SqlClient.SqlParameter("@maTT", NhanVienDTO.MaTrangThai))
        lstVal.Add(New System.Data.SqlClient.SqlParameter("@dc", NhanVienDTO.DiaChi))
        lstVal.Add(New System.Data.SqlClient.SqlParameter("@dt", NhanVienDTO.DienThoai))
        lstVal.Add(New System.Data.SqlClient.SqlParameter("@dd", NhanVienDTO.DiDong))
        lstVal.Add(New System.Data.SqlClient.SqlParameter("@email", NhanVienDTO.Email))
        lstVal.Add(New System.Data.SqlClient.SqlParameter("@ghichu", NhanVienDTO.GhiChu))

        kt = CKetNoi.ExecQueryStoreProc("ThemNhanVien", lstVal)

        Return kt
    End Function

    Public Shared Function CapNhapNhanVien(NhanVienDTO As NhanVienDTO) As Integer
        Dim kt As Integer
        Dim lstVal As New List(Of System.Data.SqlClient.SqlParameter)

        lstVal.Add(New System.Data.SqlClient.SqlParameter("@maNV", NhanVienDTO.MaNhanVien))
        lstVal.Add(New System.Data.SqlClient.SqlParameter("@tenNV", NhanVienDTO.TenNhanVien))
        lstVal.Add(New System.Data.SqlClient.SqlParameter("@maBP", NhanVienDTO.MaBoPhan))
        If NhanVienDTO.MaNguoiQL = Nothing Then
            lstVal.Add(New System.Data.SqlClient.SqlParameter("@maNQL", DBNull.Value))
        Else
            lstVal.Add(New System.Data.SqlClient.SqlParameter("@maNQL", NhanVienDTO.MaNguoiQL))
        End If
        lstVal.Add(New System.Data.SqlClient.SqlParameter("@maTT", NhanVienDTO.MaTrangThai))
        lstVal.Add(New System.Data.SqlClient.SqlParameter("@dc", NhanVienDTO.DiaChi))
        lstVal.Add(New System.Data.SqlClient.SqlParameter("@dt", NhanVienDTO.DienThoai))
        lstVal.Add(New System.Data.SqlClient.SqlParameter("@dd", NhanVienDTO.DiDong))
        lstVal.Add(New System.Data.SqlClient.SqlParameter("@email", NhanVienDTO.Email))
        lstVal.Add(New System.Data.SqlClient.SqlParameter("@ghichu", NhanVienDTO.GhiChu))

        kt = CKetNoi.ExecQueryStoreProc("CapNhatNhanVien", lstVal)

        Return kt
    End Function

    Public Shared Function XoaNhanVien(NhanVienDTO As NhanVienDTO) As Integer
        Dim kt As Integer
        Dim lstVal As New List(Of System.Data.SqlClient.SqlParameter)

        lstVal.Add(New System.Data.SqlClient.SqlParameter("@maNV", NhanVienDTO.MaNhanVien))

        kt = CKetNoi.ExecQueryStoreProc("XoaNhanVien", lstVal)

        Return kt
    End Function

    Public Shared Sub LayMaBoPhanCbx(ByVal manv As String, ByRef mabp As String, ByRef maql As String)

        DongKetNoi.Open()

        Dim cmd As New SqlCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "LayMaNhanVienCbx"
        cmd.Connection = DongKetNoi


        Dim para As SqlParameter

        para = New System.Data.SqlClient.SqlParameter("ma", SqlDbType.VarChar, 7)
        para.Direction = ParameterDirection.Input
        para.Value = manv
        cmd.Parameters.Add(para)

        para = New System.Data.SqlClient.SqlParameter("maBP", SqlDbType.VarChar, 7)
        para.Direction = ParameterDirection.Output
        cmd.Parameters.Add(para)

        para = New System.Data.SqlClient.SqlParameter("maNVQL", SqlDbType.VarChar, 7)
        para.Direction = ParameterDirection.Output
        cmd.Parameters.Add(para)

        cmd.ExecuteNonQuery()

        mabp = cmd.Parameters("maBP").Value
        maql = cmd.Parameters("maNVQL").Value

        DongKetNoi.Close()

    End Sub

    Public Shared DongKetNoi As System.Data.SqlClient.SqlConnection = CKetNoi.Connect

    Shared Function layDsNhanVien() As DataTable
        Return CKetNoi.GetDataTableByStoreProc("usp_LayDsNhanVien")
    End Function
End Class
